package com.zl.dao;

import java.sql.ResultSet;

import javax.naming.spi.DirStateFactory.Result;
import javax.swing.JOptionPane;

import java.sql.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.zl.model.BookBorrow;
import com.zl.util.ZLString;

public class BookBorrowDao {

	//增加=借书 没有删除 改==还书 查=借书记录
	
	/**
	 * 新增借阅或预约记录
	 * @param connection
	 * @param bookBorrow
	 * @return
	 * @throws Exception
	 */
	public static int add(Connection connection, BookBorrow bookBorrow) throws Exception {
	
		String sql = "insert into t_bookBorrow (userId, bookId, userName, bookName ,borrowTime, isReturn, isAppointMent, appointmentTime) values (?, ?, ?, ?, ?, ?, ?, ?)";
		
		PreparedStatement statement =  (PreparedStatement) connection.prepareStatement(sql);
		statement.setInt(1, bookBorrow.getUserId());
		statement.setInt(2, bookBorrow.getBookId());
		statement.setString(3, bookBorrow.getUserName());
		statement.setString(4, bookBorrow.getBookName());
		statement.setString(5, bookBorrow.getBorrowTime());
		statement.setBoolean(6, bookBorrow.getIsReturn());
		statement.setInt(7, bookBorrow.getIsAppointMent());
		statement.setString(8, bookBorrow.getAppointmentTime());
		
		return statement.executeUpdate();
	}
	
	/**
	 * 更新借阅状态
	 * @param connection
	 * @param bookBorrow
	 * @return
	 * @throws Exception
	 */
	public static int update(Connection connection, BookBorrow bookBorrow) throws Exception {

		String sql = "update t_bookBorrow set isReturn = ?,isAppointMent = ?, returnTime = ? where id = ?";
		
		PreparedStatement statement =  (PreparedStatement) connection.prepareStatement(sql);
		statement.setBoolean(1, bookBorrow.getIsReturn());
		statement.setInt(2, bookBorrow.getIsAppointMent());
		statement.setString(3, bookBorrow.getReturnTime());
		statement.setInt(4, bookBorrow.getId());
		
		return statement.executeUpdate();
	}
	
	/**
	 * 更新预约状态
	 * @param connection
	 * @param bookBorrow
	 * @return
	 * @throws Exception
	 */
	public static int updateAppointment(Connection connection, BookBorrow bookBorrow) throws Exception {

		String sql = "update t_bookBorrow set isAppointMent = ?, borrowTime = ? where id = ?";
		
		PreparedStatement statement =  (PreparedStatement) connection.prepareStatement(sql);
		statement.setInt(1, bookBorrow.getIsAppointMent());
		statement.setString(2, bookBorrow.getBorrowTime());
		statement.setInt(3, bookBorrow.getId());
		
		return statement.executeUpdate();
	}
	
	/**
	 * 查询借阅图书信息
	 * @param connection
	 * @param bookBorrow
	 * @return
	 * @throws Exception
	 */
	public static ResultSet select(Connection connection, BookBorrow bookBorrow) throws Exception {
		
		StringBuffer buffer = new StringBuffer("select * from t_bookborrow");
		
		if (ZLString.isNotEmpty(bookBorrow.getBookName())) {
			
			buffer.append(" and bookName like '%"+ bookBorrow.getBookName() +"%'");
		}
		
		if (ZLString.isNotEmpty(bookBorrow.getUserName())) {
			
			buffer.append(" and userName like '%"+ bookBorrow.getUserName() +"%'");
		}
		
		if (bookBorrow.getUserId() > 0) {
			buffer.append(" and userId = "+ bookBorrow.getUserId());
		}
		
		if (bookBorrow.getBookId() > 0) {
			buffer.append(" and bookId = "+ bookBorrow.getBookId());
		}
		
		buffer.append(" and isAppointMent in (0, 2)");//0表示无预约借图书 ，2表示预约借图书
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(buffer.toString().replaceFirst("and", "where"));
		
		return statement.executeQuery();
	}
	
	/**
	 * 是否可借阅
	 * @param connection
	 * @param bookBorrow
	 * @return
	 * @throws Exception
	 */
	public static boolean cloudBorrow(Connection connection, BookBorrow bookBorrow) throws Exception {
		
		StringBuffer buffer = new StringBuffer("select * from t_bookborrow");
		
		if (ZLString.isNotEmpty(bookBorrow.getBookName())) {
			
			buffer.append(" and bookName like '%"+ bookBorrow.getBookName() +"%'");
		}
		
		if (ZLString.isNotEmpty(bookBorrow.getUserName())) {
			
			buffer.append(" and userName like '%"+ bookBorrow.getUserName() +"%'");
		}
		
		if (bookBorrow.getUserId() > 0) {
			buffer.append(" and userId = "+ bookBorrow.getUserId());
		}
		
		if (bookBorrow.getBookId() > 0) {
			buffer.append(" and bookId = "+ bookBorrow.getBookId());
		}
		
		buffer.append(" and isReturn = false and isAppointMent in (0, 2)");
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(buffer.toString().replaceFirst("and", "where"));
		
		ResultSet set = statement.executeQuery();
		
		return !set.next();
	}
	
	/**
	 * 根据id查询当前用户的借阅图书
	 * @param connection
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public static ResultSet selectNotReturn(Connection connection, int userId) throws Exception {
		StringBuffer buffer = new StringBuffer("select * from t_bookborrow");
		
		buffer.append(" where isReturn = false and userId = "+ userId);

		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(buffer.toString());
		
		return statement.executeQuery();
	}
	
	/**
	 * 是否可预约
	 * @param connection
	 * @param bookBorrow
	 * @return
	 * @throws Exception
	 */
	public static boolean cloudAppointment(Connection connection, BookBorrow bookBorrow) throws Exception {
		
		StringBuffer buffer = new StringBuffer("select * from t_bookborrow");
		
		if (ZLString.isNotEmpty(bookBorrow.getBookName())) {
			
			buffer.append(" and bookName like '%"+ bookBorrow.getBookName() +"%'");
		}
		
		if (ZLString.isNotEmpty(bookBorrow.getUserName())) {
			
			buffer.append(" and userName like '%"+ bookBorrow.getUserName() +"%'");
		}
		
		if (bookBorrow.getUserId() > 0) {
			buffer.append(" and userId = "+ bookBorrow.getUserId());
		}
		
		if (bookBorrow.getBookId() > 0) {
			buffer.append(" and bookId = "+ bookBorrow.getBookId());
		}
		
		buffer.append(" and isAppointMent in (1, 2)");//1代表预约状态 2代表预约完成 也就是预约完成后的借阅状态
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(buffer.toString().replaceFirst("and", "where"));
		
		ResultSet set = statement.executeQuery();
		
		return !set.next();
	}
	
	/**
	 * 根据id查询当前用户的预约图书
	 * @param connection
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public static ResultSet selectNotAppointMent(Connection connection, int userId) throws Exception {
		StringBuffer buffer = new StringBuffer("select * from t_bookborrow");
		
		buffer.append(" where isAppointMent = 1 and userId = "+ userId);

		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(buffer.toString());
		
		return statement.executeQuery();
	}
	
}
